﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace Sinacor.Infra.Tools.ServiceRegistration.Data
{
    public class Parameter : ResourceAccess
    {
        #region Methods

        public List<Entity.Parameter> SelectParameter(Entity.Parameter entity, bool addCharacterLike)
        {
            List<Entity.Parameter> response = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_PARM, ");
            sql.AppendLine("    COD_TIPO_PARM_RETO_MET, ");
            sql.AppendLine("    COD_MET_SERV, ");
            sql.AppendLine("    NOME_PARM_MET, ");
            sql.AppendLine("    DESC_PARM_MET, ");
            sql.AppendLine("    IND_COLE, ");
            sql.AppendLine("    DATA_ALTE, ");
            sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWDPARM_MET ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("    (COD_PARM = :COD_PARM OR :COD_PARM IS NULL) AND ");
            sql.AppendLine("    (COD_TIPO_PARM_RETO_MET = :COD_TIPO_PARM_RETO_MET OR :COD_TIPO_PARM_RETO_MET IS NULL) AND ");
            sql.AppendLine("    (COD_MET_SERV = :COD_MET_SERV OR :COD_MET_SERV IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_PARM_MET) LIKE UPPER(:NOME_PARM_MET) OR :NOME_PARM_MET IS NULL) AND ");
            sql.AppendLine("    (UPPER(DESC_PARM_MET) LIKE UPPER(:DESC_PARM_MET) OR :DESC_PARM_MET IS NULL) AND ");
            sql.AppendLine("    (IND_COLE = :IND_COLE OR :IND_COLE IS NULL) ");
            sql.AppendLine("ORDER BY NOME_PARM_MET");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_PARM", DbType.Int32, entity.ParameterId);
                this.AddInParameter(command, "COD_TIPO_PARM_RETO_MET", DbType.Int32, entity.Type != null ? entity.Type.TypeId : null);
                this.AddInParameter(command, "COD_MET_SERV", DbType.Int32, entity.Method != null ? entity.Method.MethodId : null);
                this.AddInParameter(command, "NOME_PARM_MET", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.ParameterName) : entity.ParameterName);
                this.AddInParameter(command, "DESC_PARM_MET", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.Description) : entity.Description);
                this.AddInParameter(command, "IND_COLE", DbType.String, entity.IsCollection.HasValue ? entity.IsCollection.Value ? "S" : "N" : null);

                response = this.FindAll<Entity.Parameter>(command,
                    (
                        reader => new Entity.Parameter
                        {
                            ParameterId = reader.GetValue<int>("COD_PARM"),
                            ParameterName = reader.GetValue<string>("NOME_PARM_MET"),
                            Description = reader.GetValue<string>("DESC_PARM_MET"),
                            IsCollection = reader.GetValue<string>("IND_COLE") == "S",
                            Type = GetType(reader.GetValue<int>("COD_TIPO_PARM_RETO_MET")),
                            Method = entity.Method != null ? entity.Method : GetMethod(reader.GetValue<int>("COD_MET_SERV")),
                            DateChange = reader.GetValue<DateTime>("DATA_ALTE"),
                            IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX"))
                        }
                    )
                );
            }

            return response;
        }

        public Entity.Parameter InsertParameter(Entity.Parameter entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("INSERT INTO TFWDPARM_MET ( ");
            sql.AppendLine("    COD_PARM, ");
            sql.AppendLine("    COD_TIPO_PARM_RETO_MET, ");
            sql.AppendLine("    COD_MET_SERV, ");
            sql.AppendLine("    NOME_PARM_MET, ");
            sql.AppendLine("    DESC_PARM_MET, ");
            sql.AppendLine("    IND_COLE, ");
            sql.AppendLine("    DATA_ALTE ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_PARM, ");
            sql.AppendLine("    :COD_TIPO_PARM_RETO_MET, ");
            sql.AppendLine("    :COD_MET_SERV, ");
            sql.AppendLine("    :NOME_PARM_MET, ");
            sql.AppendLine("    :DESC_PARM_MET, ");
            sql.AppendLine("    :IND_COLE, ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine("    :DATA_ALTE) ");
            else
                sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_PARM", DbType.Int32, entity.ParameterId);
                this.AddInParameter(command, "COD_TIPO_PARM_RETO_MET", DbType.Int32, entity.Type != null ? entity.Type.TypeId : null);
                this.AddInParameter(command, "COD_MET_SERV", DbType.Int32, entity.Method != null ? entity.Method.MethodId : null);
                this.AddInParameter(command, "NOME_PARM_MET", DbType.String, entity.ParameterName);
                this.AddInParameter(command, "DESC_PARM_MET", DbType.String, entity.Description);
                this.AddInParameter(command, "IND_COLE", DbType.String, entity.IsCollection.HasValue ? entity.IsCollection.Value ? "S" : "N" : null);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }

            return entity;
        }

        public void UpdateParameter(Entity.Parameter entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("UPDATE TFWDPARM_MET SET ");
            sql.AppendLine("COD_TIPO_PARM_RETO_MET = :COD_TIPO_PARM_RETO_MET, ");
            sql.AppendLine("COD_MET_SERV = :COD_MET_SERV, ");
            sql.AppendLine("NOME_PARM_MET = :NOME_PARM_MET, ");
            sql.AppendLine("DESC_PARM_MET = :DESC_PARM_MET, ");
            sql.AppendLine("IND_COLE = :IND_COLE ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine(", DATA_ALTE = :DATA_ALTE ");

            sql.AppendLine("WHERE COD_PARM = :COD_PARM ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_PARM", DbType.Int32, entity.ParameterId);
                this.AddInParameter(command, "COD_TIPO_PARM_RETO_MET", DbType.Int32, entity.Type != null ? entity.Type.TypeId : null);
                this.AddInParameter(command, "COD_MET_SERV", DbType.Int32, entity.Method != null ? entity.Method.MethodId : null);
                this.AddInParameter(command, "NOME_PARM_MET", DbType.String, entity.ParameterName);
                this.AddInParameter(command, "DESC_PARM_MET", DbType.String, entity.Description);
                this.AddInParameter(command, "IND_COLE", DbType.String, entity.IsCollection.HasValue ? entity.IsCollection.Value ? "S" : "N" : null);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteParameter(Entity.Parameter entity)
        {
            string sql = "DELETE FROM TFWDPARM_MET WHERE COD_PARM = :COD_PARM";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_PARM", DbType.Int32, entity.ParameterId);
                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteParameterByMethodId(Entity.Parameter entity)
        {
            string sql = "DELETE FROM TFWDPARM_MET WHERE COD_MET_SERV = :COD_MET_SERV";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_MET_SERV", DbType.Int32, entity.Method.MethodId);
                this.ExecuteNonQuery(command);
            }
        }

        public int GetNextParameterId()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(COD_PARM) + 1, 1) FROM TFWDPARM_MET ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }
    
        private Entity.Type GetType(int id_type)
        {
            Type t = new Type();
            List<Entity.Type> responseList = null;
            Entity.Type response = null;

            if (id_type != 0)
            {
                responseList = t.SelectType(new Entity.Type { TypeId = id_type }, false);

                if (responseList != null && responseList.Count > 0)
                    response = responseList[0];
            }

            return response;
        }

        private Entity.Method GetMethod(int id_method)
        {
            Method t = new Method();
            List<Entity.Method> responseList = null;
            Entity.Method response = null;

            if (id_method != 0)
            {
                responseList = t.SelectMethod(new Entity.Method { MethodId = id_method }, false);

                if (responseList != null && responseList.Count > 0)
                    response = responseList[0];
            }

            return response;
        }

        #endregion
    }
}